package com.css.fxfzdzzh.modules.basicData.repository.impl;

import com.css.fxfzdzzh.constants.YNEnum;
import com.css.fxfzdzzh.modules.basicData.entity.LandForm;
import com.css.fxfzdzzh.modules.basicData.entity.NaturalDensity;
import com.css.fxfzdzzh.modules.basicData.vo.QueryByVersionParam;
import com.css.fxfzdzzh.util.PlatformObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

/**
 * @author Dbj
 * @date 2023年02月27日  15:42
 */
@Repository
public class NaturalDensityRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    public void byShpBatchSave(List<NaturalDensity> list) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray());
        String sql = "insert into dzzh_sys_natural_density  values(" +
                ":dsnId,:dsnNumber,:dsnName,:delFlag,:createUser,:createTime,:updateUser,:updateTime,:dataVersion,ST_GeometryFromText(:geom,4490))";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    public void deleteByVersion(String versionCode) {
        String sql = "update dzzh_sys_natural_density  set del_flag = '1' where data_version='" + versionCode + "' and del_flag='"+YNEnum.N+"'";
        jdbcTemplate.update(sql);
    }

    public void updateLs(String versionCode, String tempVersionCode) {
        String sql = "update dzzh_sys_natural_density  set del_flag = '0' ,data_version= '"+versionCode+"'  where data_version='" + tempVersionCode + "'";
        jdbcTemplate.update(sql);
    }

    public void updateByVersion(String tempVersionCode, String versionCode, Integer dataRangeCountry) {
        String sql = "update dzzh_sys_natural_density  set del_flag = '0' ,data_version= '"+versionCode+"' where data_version='" + tempVersionCode + "'";
        jdbcTemplate.update(sql);
    }

    public List<NaturalDensity> getYwData(QueryByVersionParam queryByVersionParam) {
        StringBuilder stringBuilder=new StringBuilder("select * from  dzzh_sys_natural_density where 1=1 and data_version='"+queryByVersionParam.getVersionCode()+"' and del_flag='"+ YNEnum.N.toString()+"' ");
        queryByDataVersionSQLBuild(queryByVersionParam, stringBuilder);
        stringBuilder.append(" ORDER BY CAST(dsn_number AS DECIMAL) asc ");
        if (queryByVersionParam.getPage() != null && queryByVersionParam.getRows() != null) {
            stringBuilder.append(" limit ").append(queryByVersionParam.getRows()).append(" offset ").append(queryByVersionParam.getRows() * (Math.max(queryByVersionParam.getPage(), 1) - 1));
        }
        return jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(NaturalDensity.class));
    }

    private void queryByDataVersionSQLBuild(QueryByVersionParam queryByVersionParam,StringBuilder stringBuilder) {
        if (org.apache.commons.lang3.StringUtils.isNotBlank(queryByVersionParam.getProvince())) {
            stringBuilder.append(" and province = '").append(queryByVersionParam.getProvince()).append("' ");
        }
        if (org.apache.commons.lang3.StringUtils.isNotBlank(queryByVersionParam.getCity())) {
            stringBuilder.append(" and city = '").append(queryByVersionParam.getCity()).append("' ");
        }
        if (StringUtils.isNotBlank(queryByVersionParam.getCounty())) {
            stringBuilder.append(" and county = '").append(queryByVersionParam.getCounty()).append("' ");
        }
    }

    public Long countByDataVersion(QueryByVersionParam queryByVersionParam) {
        StringBuilder sql = new StringBuilder("select count(*) from dzzh_sys_natural_density where del_flag = '0' and data_version = '" + queryByVersionParam.getVersionCode() + "'");
        queryByDataVersionSQLBuild(queryByVersionParam, sql);
        return jdbcTemplate.queryForObject(sql.toString(), Long.class);
    }

    public List<NaturalDensity> getYwData(String versionCode, String geom) {
        StringBuilder stringBuilder=new StringBuilder("select *," + (geom == null ? ",ST_AsText(geom) geom" : "," + "st_Astext(St_intersection(st_geomfromtext(" + geom + ",4490),geom)) geom") + " geom from  dzzh_sys_natural_density where 1=1" + (geom == null ? "" : " and st_intersects(st_geomfromtext(" + geom + ",4490),geom)") + " and data_version='"+versionCode+"' and del_flag='"+ YNEnum.N.toString()+"' ");
        return jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(NaturalDensity.class));
    }

    public Integer getValidDataNum(String versionCode) {
        String sql = "select count(*) from dzzh_sys_natural_density where del_flag='0' and data_version='"+versionCode+"'";
        Integer rowNum = jdbcTemplate.queryForObject(sql, Integer.class);
        if(rowNum==null){
            return 0;
        }
        return rowNum;
    }

    public NaturalDensity getYwDetails(String id) {
        StringBuilder stringBuilder=new StringBuilder("select * from  dzzh_sys_natural_density where 1=1 and dsn_id='"+id+"' and del_flag='"+ YNEnum.N.toString()+"' ");
        List<NaturalDensity> query = jdbcTemplate.query(stringBuilder.toString(), new BeanPropertyRowMapper<>(NaturalDensity.class));
        return PlatformObjectUtils.isNotEmpty(query)?query.get(0):new NaturalDensity();
    }
}
